﻿using DbSync.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace DbSync.Provider
{
    public class MySqlProxy : IDbProxy
    {
        public bool ConnectStatus { get; }
        public DBHelper DB { get; }

        public MySqlProxy(string connString)
        {
            try
            {               
                var  ConnectionString = connString + "Convert Zero Datetime=True;Allow Zero Datetime=True;charset=utf8;Min Pool Size=10;Max Pool Size=512;";
                 DB = new DBHelper(ConnectionString);                
                 ConnectStatus = DB.CanConnect();
            }
            catch (Exception ex)
            {
                ConnectStatus = false;
                LogHelper.Error(ex.Message, "MySqlProxy");
            }
        }

            

        public bool CreateTable(string createTableSql)
        {
            //string createSql = this.getDatas("show create table " + tableName).Select(item => item[1]).First();
            return this.Execute(createTableSql)>=0;
        }

        //删除数据
        public int DeleteData(string tableName, string pkey, List<string> ids)
        {
           return  this.Execute("delete from  " + tableName +" where "+ pkey + " in ('','','')");
        }

        public void Dispose()
        {
            //this.DB.Close();
        }
        //删除表
        public int DropTable(string tableName)
        {
           return this.Execute("drop table " + tableName);
        }

        public Dictionary<string, string> GetIndexMap(string tableName)
        {
            List<List<string>> indexStrStrs = this.getDatas("show keys from " + tableName);
            Dictionary<string, string> indexMap = new Dictionary<string, string>();

            foreach (List<string> indexStrs in indexStrStrs)
            {
                string nonUnique = indexStrs[1];
                string keyName = indexStrs[2];
                string columnBame = indexStrs[4];

                if (!indexMap.ContainsKey(keyName))
                {
                    if (keyName == "PRIMARY")
                    {
                        indexMap[keyName] = "primary key (";
                    }
                    else if (nonUnique == "0")
                    {
                        indexMap[keyName] = "unique " + keyName + " (";
                    }
                    else
                    {
                        indexMap[keyName] = "index " + keyName + " (";
                    }
                }
                else
                {
                    indexMap[keyName] += ",";
                }

                indexMap[keyName] += columnBame;
            }

            string[] keys = indexMap.Keys.ToArray();
            foreach (string key in keys)
            {
                indexMap[key] += ")";
            }
            return indexMap;
        }

        public List<Dictionary<string, string>> GetTableData(string tableName)
        {
            return this.getDatasDic("select * from "+ tableName+";");
        }
        public List<Dictionary<string, string>> GetTableDataByKeys(string tableName,string keyName,List<string> keys)
        {
            string querySql = "select * from " + tableName + " where " + keyName + " in (" + string.Join(",", keys) + ");";
            return this.getDatasDic(querySql);
        }

        public List<Dictionary<string, string>> GetTableDataByPage(string tableName, int pageindex, int pagesize, string pkey, string extraField = "pkIndex", KeyValuePair<string, string> orderby= new KeyValuePair<string, string>())
        {
            var pageSql = "select * from `{0}` a join (select {1} as '{4}' from `{0}` limit {2}, {3}) b on a.{1}=b.{4};";
            if (!string.IsNullOrEmpty(orderby.Key) )
            {
                if (!string.IsNullOrEmpty(orderby.Value))
                {
                    pageSql = "select * from `{0}` a join (select {1} as '{4}' from `{0}` where `{5}`>='{6}' order by `{5}`   limit {2}, {3}) b on a.{1}=b.{4};";
                }
                else
                {
                    pageSql = "select * from `{0}` a join (select {1} as '{4}' from `{0}`  order by `{5}`  limit {2}, {3}) b on a.{1}=b.{4};";
                }
                
            }
            var pageSqlExcuteString = string.Format(pageSql, tableName, pkey, pageindex * pagesize, pagesize, extraField, orderby.Key,orderby.Value);
            return this.getDatasDic(pageSqlExcuteString,new List<string>(){ extraField});
        }

        public string GetCreateTableInfo(string tableName)
        {
            return  this.getDatas("show create table " + tableName).Select(item => item[1]).First();
        }

        public List<string> GetTables()
        {
           return  this.getDatas("show tables").Select(item=>item.First()).ToList();
        }

        public Dictionary<string, string> GetTableStructure(string tableName)
        {
            var key = "";
            var auto_increment = "";
            return GetTableStructure(tableName, out key,out auto_increment);
        }
        public Dictionary<string, string> GetTableStructure(string tableName, out string key, out string auto_increment)
        {
            key = "";
            auto_increment = "";
            Dictionary<string, string> columnMap = new Dictionary<string, string>();
            var colums = this.getDatasDic("SHOW FULL COLUMNS FROM " + tableName) ;
            foreach (var item in colums)
            {
                if (item["Key"] == "PRI")
                {
                    key = item["Field"];
                }
                string createStr = "`" + item["Field"] + "` " + item["Type"] + "";
                if (item["Null"] == "NO")
                {
                    createStr += " NOT NULL ";
                }
                if(item["Extra"] == "auto_increment")
                {
                    //自增列
                    createStr += " AUTO_INCREMENT ";
                    auto_increment = key;
                }
                if (!string.IsNullOrEmpty(item["Default"]))
                {
                    createStr += " DEFAULT '" + item["Default"] + "'";
                }
                if (!string.IsNullOrEmpty(item["Comment"]))
                {
                    createStr += " COMMENT '" + item["Comment"] + "'";
                }
                if (!string.IsNullOrEmpty(auto_increment))
                {
                    //自增索引
                    createStr += " ,ADD PRIMARY KEY (`"+ auto_increment + "`) ";
                }
                columnMap[item["Field"]] = createStr;
            }
            return columnMap;
        }

        public int InsertData(string table, List<Dictionary<string, string>> datas)
        {
            var Columns = new List<string>();
            var Values = new List<string>();
            List<string> tableFieldList = new List<string>();
            foreach (var item in datas)
            {
                //字段名称
                if (Columns.Count == 0)
                {
                    Columns = item.Keys.Select(k => "`" + k + "`").ToList();
                }
                if (tableFieldList.Count == 0)
                {
                    tableFieldList = item.Keys.Select(k => "`" + k + "`" + "=" + "values(" + "`" + k + "`" + ")").ToList();
                }
                //转义特殊字符
                var valueList = item.Values.Select(v =>
                {
                    v = v.Replace("\\", "\\\\").Replace("'", "\\'");
                    return "'" + v + "'";
                }).ToList();

                string value = "(" + string.Join(",", valueList) + ")";
                Values.Add(value);
            }

              
            string sql = "insert into " + table + "(" + string.Join(",", Columns) + ") values"
                                   + string.Join(",", Values) + " on duplicate key update " + string.Join(",", tableFieldList) + ";";
            var result = this.Execute(sql);

            return result;

        }

       

        public int UpdateData(string table, List<Dictionary<string, string>> datas)
        {
            var Columns = new List<string>();
            var Values = new List<string>();
            List<string> tableFieldList = new List<string>();
            foreach (var item in datas)
            {
                //字段名称
                if (Columns.Count == 0)
                {
                    Columns = item.Keys.Select(k => "`" + k + "`").ToList();
                }
                if (tableFieldList.Count == 0)
                {
                    tableFieldList = item.Keys.Select(k => "`" + k + "`" + "=" + "values(" + "`" + k + "`" + ")").ToList();
                }
                //转义特殊字符
                var valueList = item.Values.Select(v =>
                {
                    v = v.Replace("\\", "\\\\").Replace("'", "\\'");
                    return "'" + v + "'";
                }).ToList();

                string value = "(" + string.Join(",", valueList) + ")";
                Values.Add(value);
            }


            string sql = "insert into " + table + "(" + string.Join(",", Columns) + ") values"
                                   + string.Join(",", Values) + " on duplicate key update " + string.Join(",", tableFieldList) + ";";
            var result = this.Execute(sql);

            return result/2;
        }

        public int CurrentConnectedCount()
        {
            //Variable_name  Value
            var datas = this.getDatasDic("show status like 'Threads%';");
            foreach (var dic in datas) { 
              foreach( var item in dic.Keys)
                {
                  if(dic["Variable_name"]== "Threads_running")
                    {
                        return int.Parse(dic["Value"]);
                    }
                }
            }
            return -1;
        }

        public int LimitConnectedCount()
        {
            return int.Parse(this.getData("show variables like '%max_connections%';", 1));

            //return 0;//this.getDatas("show variables like '%max_connections%';");
        }

        public List<List<string>> getDatas(string sql)
        {
            List<List<string>> datas = new List<List<string>>();
            //获取整个表的数据
            try
            {
                DataTable table = DB.ExecuteDataTable(sql); ;
                foreach (DataRow row in table.Rows)
                {
                    List<string> temp = new List<string>();
                    foreach (DataColumn column in table.Columns)
                    {
                        temp.Add(row[column].ToString());
                    }
                    datas.Add(temp);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.Message, "MysqlProxy");
            }
            return datas;
        }

        /// <summary>
        /// 数据查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="columnSelect">字段筛选</param>
        /// <param name="columnSelectType">1:忽略(默认),0:包含</param>
        /// <returns></returns>
        public List<Dictionary<string, string>> getDatasDic(string sql, List<string> columnSelect = null, int columnSelectType = 1)
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
            try
            {
                result= DB.ExecuteReader(sql, (reader) =>
                {
                    List<Dictionary<string, string>> datas = new List<Dictionary<string, string>>();
                    while (reader.Read())
                    {
                        Dictionary<string, string> temp = new Dictionary<string, string>();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var dicKey = reader.GetName(i);
                            if (columnSelect != null)
                            {
                                if (columnSelectType == 1 && columnSelect.Contains(dicKey))
                                {
                                    continue;
                                }
                                if (columnSelectType == 0 && !columnSelect.Contains(dicKey))
                                {
                                    continue;
                                }
                            }

                            string value = string.Empty;
                            //类型转换
                            var DataType = reader.GetDataTypeName(i);
                            switch (DataType)
                            {
                                case "DATETIME":
                                    DateTime dt_datetime = new DateTime();
                                    DateTime.TryParse(reader[i].ToString(), out dt_datetime);
                                    value = dt_datetime.ToString("yyyy-MM-dd HH:mm:ss");
                                    break;
                                case "DATE":
                                    DateTime dt_date = new DateTime();
                                    DateTime.TryParse(reader[i].ToString(), out dt_date);
                                    value = dt_date.ToString("yyyy-MM-dd");
                                    break;
                                case "CHAR(36)":
                                    Guid gd = new Guid();
                                    Guid.TryParse(reader[i].ToString(), out gd);
                                    value = gd.ToString();
                                    break;
                                case "INT":
                                case "DOUBLE":
                                    value = string.IsNullOrEmpty(reader[i].ToString()) ? "0" : reader[i].ToString();
                                    break;
                                default:
                                    //Type t = reader.GetFieldType(i);
                                    //System.Activator.CreateInstance(t);
                                    //t.Assembly.CreateInstance("");
                                    value = reader[i].ToString();
                                    break;
                            }
                            temp.Add(dicKey, value);
                        }
                        datas.Add(temp);
                    }
                    return datas;
                });
                return result;
            }
            catch (Exception ex)
            {
                
                LogHelper.Error(ex.Message + "\r\n" + sql, "MysqlProxy");
                return result;
            }
            finally
            {
               // DB.Close();
            }
        }
        //执行sql语句
        public int Execute(string sql)
        {
            try
            {
                var result= this.DB.ExecuteNonQuery(sql);             
                LogHelper.Debug(sql, "MysqlProxy");
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.Message + "\r\n" + sql, "MysqlProxy");
                return 0;
            }
        }

        public string getData(string sql, int index = 0)
        {
            if (index > 0)
            {
                DataTable table = DB.ExecuteDataTable(sql);
                if(table.Rows.Count>0||table.Columns.Count> index)
                {
                    return table.Rows[0][index].ToString();
                }
                return "-1";
            }
            else
            {
                return DB.ExecuteScalar(sql).ToString();
            }            
        }

        public int AddTableColumn(string tableName, KeyValuePair<string, string> column)
        {
            if (column.Value.Contains("AUTO_INCREMENT"))
            {
                Execute("alter table " + tableName + " drop primary key");
            }
            return Execute("alter table " + tableName + " add " + column.Value);
        }

        public int ModifyTableColumn(string tableName, KeyValuePair<string, string> column)
        {
            return Execute("alter table " + tableName + " modify " + column.Value);
        }

        public int DeleteTableColumn(string tableName, KeyValuePair<string, string> column)
        {
            return Execute("alter table " + tableName + " drop " + column.Key);
        }

        public int AddTableIndex(string tableName, KeyValuePair<string, string> index)
        {
            //判断原来是否包含主键索引
            if (index.Key == "PRIMARY")
            {
               Execute("alter table " + tableName + " drop primary key");                
            }
                //格式转换
            var faValue = index.Value.Replace(index.Key, "`"+ index.Key + "`");
            return Execute("alter table " + tableName + " add  " + faValue);
            
        }

        public int ModifyTableIndex(string tableName, KeyValuePair<string, string> index)
        {
            
            if (index.Key== "PRIMARY")
            {
                return Execute("alter table " + tableName + " drop primary key, add " + index.Value);
            }
            else
            {
                var faValue = index.Value.Replace(index.Key, "`" + index.Key + "`");
                return Execute("alter table " + tableName + " drop index `" + index .Key+ "`, add " + faValue);
            }
            
        }

        public int DeleteTableIndex(string tableName, KeyValuePair<string, string> index)
        {
            if (index.Key == "PRIMARY")
            {
                return Execute("alter table " + tableName + " drop primary key");
            }
            else
            {
                return Execute("alter table " + tableName + " drop index " + index.Key);
            }
        }
    }
}
